{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sklearn.decomposition import PCA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "#读取四张表的数据\n",
    "prior = pd.read_csv(\"./data/instacart/order_products__prior.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "products = pd.read_csv(\"./data/instacart/products.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "orders = pd.read_csv(\"./data/instacart/orders.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "aisles = pd.read_csv(\"./data/instacart/aisles.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "#合并四张表到一张表中\n",
    "_mg = pd.merge(prior,products,on=['product_id','product_id'])\n",
    "_mg = pd.merge(_mg,orders,on=['order_id','order_id'])\n",
    "mt = pd.merge(_mg,aisles, on=['aisle_id','aisle_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "   order_id  product_id  add_to_cart_order  reordered        product_name  \\\n0         2       33120                  1          1  Organic Egg Whites   \n1        26       33120                  5          0  Organic Egg Whites   \n2       120       33120                 13          0  Organic Egg Whites   \n3       327       33120                  5          1  Organic Egg Whites   \n4       390       33120                 28          1  Organic Egg Whites   \n5       537       33120                  2          1  Organic Egg Whites   \n6       582       33120                  7          1  Organic Egg Whites   \n7       608       33120                  5          1  Organic Egg Whites   \n8       623       33120                  1          1  Organic Egg Whites   \n9       689       33120                  4          1  Organic Egg Whites   \n\n   aisle_id  department_id  user_id eval_set  order_number  order_dow  \\\n0        86             16   202279    prior             3          5   \n1        86             16   153404    prior             2          0   \n2        86             16    23750    prior            11          6   \n3        86             16    58707    prior            21          6   \n4        86             16   166654    prior            48          0   \n5        86             16   180135    prior            15          2   \n6        86             16   193223    prior             6          2   \n7        86             16    91030    prior            11          3   \n8        86             16    37804    prior            63          3   \n9        86             16   108932    prior            16          1   \n\n   order_hour_of_day  days_since_prior_order aisle  \n0                  9                     8.0  eggs  \n1                 16                     7.0  eggs  \n2                  8                    10.0  eggs  \n3                  9                     8.0  eggs  \n4                 12                     9.0  eggs  \n5                  8                     3.0  eggs  \n6                 19                    10.0  eggs  \n7                 21                    12.0  eggs  \n8                 12                     3.0  eggs  \n9                 13                     3.0  eggs  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>order_id</th>\n      <th>product_id</th>\n      <th>add_to_cart_order</th>\n      <th>reordered</th>\n      <th>product_name</th>\n      <th>aisle_id</th>\n      <th>department_id</th>\n      <th>user_id</th>\n      <th>eval_set</th>\n      <th>order_number</th>\n      <th>order_dow</th>\n      <th>order_hour_of_day</th>\n      <th>days_since_prior_order</th>\n      <th>aisle</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2</td>\n      <td>33120</td>\n      <td>1</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>202279</td>\n      <td>prior</td>\n      <td>3</td>\n      <td>5</td>\n      <td>9</td>\n      <td>8.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>26</td>\n      <td>33120</td>\n      <td>5</td>\n      <td>0</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>153404</td>\n      <td>prior</td>\n      <td>2</td>\n      <td>0</td>\n      <td>16</td>\n      <td>7.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>120</td>\n      <td>33120</td>\n      <td>13</td>\n      <td>0</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>23750</td>\n      <td>prior</td>\n      <td>11</td>\n      <td>6</td>\n      <td>8</td>\n      <td>10.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>327</td>\n      <td>33120</td>\n      <td>5</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>58707</td>\n      <td>prior</td>\n      <td>21</td>\n      <td>6</td>\n      <td>9</td>\n      <td>8.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>390</td>\n      <td>33120</td>\n      <td>28</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>166654</td>\n      <td>prior</td>\n      <td>48</td>\n      <td>0</td>\n      <td>12</td>\n      <td>9.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>537</td>\n      <td>33120</td>\n      <td>2</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>180135</td>\n      <td>prior</td>\n      <td>15</td>\n      <td>2</td>\n      <td>8</td>\n      <td>3.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>582</td>\n      <td>33120</td>\n      <td>7</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>193223</td>\n      <td>prior</td>\n      <td>6</td>\n      <td>2</td>\n      <td>19</td>\n      <td>10.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>608</td>\n      <td>33120</td>\n      <td>5</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>91030</td>\n      <td>prior</td>\n      <td>11</td>\n      <td>3</td>\n      <td>21</td>\n      <td>12.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>623</td>\n      <td>33120</td>\n      <td>1</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>37804</td>\n      <td>prior</td>\n      <td>63</td>\n      <td>3</td>\n      <td>12</td>\n      <td>3.0</td>\n      <td>eggs</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>689</td>\n      <td>33120</td>\n      <td>4</td>\n      <td>1</td>\n      <td>Organic Egg Whites</td>\n      <td>86</td>\n      <td>16</td>\n      <td>108932</td>\n      <td>prior</td>\n      <td>16</td>\n      <td>1</td>\n      <td>13</td>\n      <td>3.0</td>\n      <td>eggs</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "execute_result",
     "execution_count": 83
    }
   ],
   "source": [
    "mt.head(10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "交叉表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "cross = pd.crosstab(mt['user_id'],mt['aisle'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "aisle    air fresheners candles  asian foods  baby accessories  \\\nuser_id                                                          \n1                             0            0                 0   \n2                             0            3                 0   \n3                             0            0                 0   \n4                             0            0                 0   \n5                             0            2                 0   \n6                             0            0                 0   \n7                             0            0                 0   \n8                             0            1                 0   \n9                             0            0                 0   \n10                            0            1                 0   \n\naisle    baby bath body care  baby food formula  bakery desserts  \\\nuser_id                                                            \n1                          0                  0                0   \n2                          0                  0                0   \n3                          0                  0                0   \n4                          0                  0                0   \n5                          0                  0                0   \n6                          0                  0                0   \n7                          0                  0                0   \n8                          0                  0                0   \n9                          0                  6                0   \n10                         0                  0                0   \n\naisle    baking ingredients  baking supplies decor  beauty  beers coolers  \\\nuser_id                                                                     \n1                         0                      0       0              0   \n2                         2                      0       0              0   \n3                         0                      0       0              0   \n4                         0                      0       0              0   \n5                         0                      0       0              0   \n6                         0                      0       0              0   \n7                         2                      0       0              0   \n8                         1                      0       0              0   \n9                         2                      0       0              0   \n10                        0                      0       0              0   \n\naisle    ...  spreads  tea  tofu meat alternatives  tortillas flat bread  \\\nuser_id  ...                                                               \n1        ...        1    0                       0                     0   \n2        ...        3    1                       1                     0   \n3        ...        4    1                       0                     0   \n4        ...        0    0                       0                     1   \n5        ...        0    0                       0                     0   \n6        ...        0    0                       0                     0   \n7        ...        0    0                       0                     0   \n8        ...        0    0                       0                     0   \n9        ...        0    0                       0                     0   \n10       ...        0    0                       0                     0   \n\naisle    trail mix snack mix  trash bags liners  vitamins supplements  \\\nuser_id                                                                 \n1                          0                  0                     0   \n2                          0                  0                     0   \n3                          0                  0                     0   \n4                          0                  0                     0   \n5                          0                  0                     0   \n6                          0                  0                     0   \n7                          0                  0                     0   \n8                          0                  0                     0   \n9                          0                  0                     0   \n10                         0                  0                     0   \n\naisle    water seltzer sparkling water  white wines  yogurt  \nuser_id                                                      \n1                                    0            0       1  \n2                                    2            0      42  \n3                                    2            0       0  \n4                                    1            0       0  \n5                                    0            0       3  \n6                                    0            0       0  \n7                                    0            0       5  \n8                                    0            0       0  \n9                                    2            0      19  \n10                                   0            0       2  \n\n[10 rows x 134 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th>aisle</th>\n      <th>air fresheners candles</th>\n      <th>asian foods</th>\n      <th>baby accessories</th>\n      <th>baby bath body care</th>\n      <th>baby food formula</th>\n      <th>bakery desserts</th>\n      <th>baking ingredients</th>\n      <th>baking supplies decor</th>\n      <th>beauty</th>\n      <th>beers coolers</th>\n      <th>...</th>\n      <th>spreads</th>\n      <th>tea</th>\n      <th>tofu meat alternatives</th>\n      <th>tortillas flat bread</th>\n      <th>trail mix snack mix</th>\n      <th>trash bags liners</th>\n      <th>vitamins supplements</th>\n      <th>water seltzer sparkling water</th>\n      <th>white wines</th>\n      <th>yogurt</th>\n    </tr>\n    <tr>\n      <th>user_id</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>1</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>0</td>\n      <td>3</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>3</td>\n      <td>1</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>42</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>4</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>6</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n      <td>0</td>\n      <td>19</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>...</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2</td>\n    </tr>\n  </tbody>\n</table>\n<p>10 rows × 134 columns</p>\n</div>"
     },
     "metadata": {},
     "output_type": "execute_result",
     "execution_count": 85
    }
   ],
   "source": [
    "cross.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "主成分分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "pca = PCA(n_components=0.9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "data = pca.fit_transform(cross)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "pycharm": {
     "is_executing": false,
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "array([[-2.42156587e+01,  2.42942720e+00, -2.46636975e+00, ...,\n         6.86800336e-01,  1.69439402e+00, -2.34323022e+00],\n       [ 6.46320806e+00,  3.67511165e+01,  8.38255336e+00, ...,\n         4.12121252e+00,  2.44689740e+00, -4.28348478e+00],\n       [-7.99030162e+00,  2.40438257e+00, -1.10300641e+01, ...,\n         1.77534453e+00, -4.44194030e-01,  7.86665571e-01],\n       ...,\n       [ 8.61143331e+00,  7.70129866e+00,  7.95240226e+00, ...,\n        -2.74252456e+00,  1.07112531e+00, -6.31925661e-02],\n       [ 8.40862199e+01,  2.04187340e+01,  8.05410372e+00, ...,\n         7.27554259e-01,  3.51339470e+00, -1.79079914e+01],\n       [-1.39534562e+01,  6.64621821e+00, -5.23030367e+00, ...,\n         8.25329076e-01,  1.38230701e+00, -2.41942061e+00]])"
     },
     "metadata": {},
     "output_type": "execute_result",
     "execution_count": 88
    }
   ],
   "source": [
    "data\n",
    "\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  },
  "pycharm": {
   "stem_cell": {
    "cell_type": "raw",
    "metadata": {
     "collapsed": false
    },
    "source": []
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}